with unsign_trace_waybill_step as (
    select t1.waybill_no                                                            as waybill_no
         , input_time
         , send_station_network_code                                                as send_station_network_code       --当前站点code
         , send_station_network_name                                                as send_station_network_name       --当前站点code
         , send_station_network_type                                                as send_station_network_type       --当前站点类型
         , send_station_send_later_time                                             as send_station_send_later_time    --当前站点发件时间
         , send_station_arrival_early_time                                          as send_station_arrival_early_time --当前站点到件时间
         , rn                                                                       as rn
         , no_send_arr_flag                                                         as no_send_arr_flag                --无到发时间
         , min_send_arr                                                             as min_send_arr                    --到发最小
         , min_scan_time                                                            as min_scan_time                   --操作最小
         , lead(send_station_network_code)
                over (partition by t1.waybill_no order by rn)                       as next_station_network_code       --下一站点code
         , lead(send_station_network_type)
                over (partition by t1.waybill_no order by rn)                       as next_station_network_type       --下一站点类型
         , lead(send_station_send_later_time)
                over (partition by t1.waybill_no order by rn)                       as next_station_send_later_time    --下一站点发件时间
         , lead(send_station_arrival_early_time)
                over (partition by t1.waybill_no order by rn)                       as next_station_arrival_early_time --下一站点到件时间
         , first_value(min_scan_time) over (partition by t1.waybill_no order by rn) as first_scan_time                 --首机构扫描时间
         , first_value(send_station_network_code)
                       over (partition by t1.waybill_no order by rn)                as first_network_code              --首机构网点
    from (
             select waybill_no                                                               as waybill_no
                  , input_time
                  , send_station_number                                                      as send_station_number             --轨迹排序
                  , send_center_station_number                                               as send_center_station_number      --中心轨迹排序
                  , send_station_network_code                                                as send_station_network_code       --当前站点code
                  , send_station_network_name                                                as send_station_network_name       --当前站点code
                  , send_station_network_type                                                as send_station_network_type       --当前站点类型
                  , send_station_send_later_time                                             as send_station_send_later_time    --当前站点发件时间
                  , send_station_arrival_early_time                                          as send_station_arrival_early_time --当前站点到件时间
                  , if(send_station_send_later_time is null and send_station_arrival_early_time is null, 1,
                       0)                                                                    as no_send_arr_flag                --无到发时间
                  , least(nvl(send_station_send_later_time, '9999-01-01 01:00:00'),
                          nvl(send_station_arrival_early_time, '9999-01-01 01:00:00'))       as min_send_arr                    --到发最小
                  , least(nvl(send_station_send_later_time, '9999-01-01 01:00:00'),
                          nvl(send_station_arrival_early_time, '9999-01-01 01:00:00'),
                          cast(taking_later_scantime as string))                             as min_scan_time
                  , row_number() over (partition by waybill_no order by send_station_number) as rn
--                   , taking_later_scantime                                                    as taking_early_scantime           --揽收时间
--                   , taking_early_scansitecode                                                as taking_early_scansitecode       --揽收网点
--                   , deliver_early_scantime                                                   as deliver_early_scantime
--                   , next_station_send_later_time                                             as next_station_send_later_time
--                   , next_station_arrival_early_time                                          as next_station_arrival_early_time
--                   , instore_early_scantime                                                   as instore_early_scantime
--                   , outstore_early_scantime                                                  as outstore_early_scantime
--                   , sign_early_scantime                                                      as sign_early_scantime
                  , dt                                                                       as dt
             from jms_dwd.dwd_wide_unsign_trace_waybill_step_dt
             where dt >= date_add('{{ execution_date | cst_ds }}', -30)
               and dt <= '{{ execution_date | cst_ds }}'
               and substr(input_time, 1, 10) >= date_add('{{ execution_date | cst_ds }}', -30)
               and substr(input_time, 1, 10) <= '{{ execution_date | cst_ds }}'
               and input_time is not null
--                and ordersource_name = '桃花岛'
               and order_source_code = 'D09'
               and (
                     (--首轨迹,且是网点,且有做发件
                             send_station_number = 1 and send_station_network_type = 4 and
                             send_station_send_later_time is not null
                         )
                     or
                     (--首轨迹,且集散or中心,且有到or有发
                             send_station_number = 1 and send_station_network_type <> 4 and
                             (send_station_send_later_time is not null or
                              send_station_arrival_early_time is not null)
                         )
                     or
                     (--非首轨迹,网点or集散or中心,有到or有发
                             send_station_number <> 1 and (send_station_send_later_time is not null or
                                                           send_station_arrival_early_time is not null)
                         )
                     or (send_station_network_code = taking_early_scansitecode)
                 )
--                and nvl(least(send_station_send_later_time, send_station_arrival_early_time,
--                              next_station_send_later_time, next_station_arrival_early_time), '9999-01-01 01:00:00') <=
--                    nvl(deliver_early_scantime, '9999-01-01 01:00:00')
               and least(nvl(send_station_send_later_time, '9999-01-01 01:00:00'),
                         nvl(send_station_arrival_early_time, '9999-01-01 01:00:00'),
                         nvl(next_station_send_later_time, '9999-01-01 01:00:00'),
                         nvl(next_station_arrival_early_time, '9999-01-01 01:00:00')) <=
                   least(nvl(deliver_early_scantime, '9999-01-01 01:00:00'),
                         nvl(instore_early_scantime, '9999-01-01 01:00:00'),
                         nvl(outstore_early_scantime, '9999-01-01 01:00:00'),
                         nvl(sign_early_scantime, '9999-01-01 01:00:00'))
--                and least(nvl(send_station_send_later_time, '9999-01-01 01:00:00'),
--                          nvl(send_station_arrival_early_time, '9999-01-01 01:00:00')) >=
--                    nvl(taking_later_scantime, '9999-01-01 01:00:00')
               and least(nvl(send_station_send_later_time, '9999-01-01 01:00:00'),
                         nvl(send_station_arrival_early_time, '9999-01-01 01:00:00')) >=
                   if(nvl(taking_later_scantime, '-99') = nvl(taking_early_scantime, '-99'),
                      '0000-01-01 01:00:00', nvl(taking_later_scantime, '9999-01-01 01:00:00'))
         ) t1
             left anti
             join
         (
             select waybill_no
             from jms_dwd.dwd_tab_reback_transfer_express_base_dt
             where dt >= date_add('{{ execution_date | cst_ds }}', -30)
               and dt <= '{{ execution_date | cst_ds }}'
               and status not in (3, 4) --状态 1待审核，2已审核,3 取消申请,4.驳回
             group by waybill_no
         ) t2
         on t1.waybill_no = t2.waybill_no
             left anti
             join
         (
             select waybill_id
             from jms_dwd.dwd_yl_oms_order_mark_expand_base_dt
             where dt >= date_add('{{ execution_date | cst_ds }}', -35)
               and dt <= '{{ execution_date | cst_ds }}'
               and field_name = 'reverseMail'
             group by waybill_id
         ) t3
         on t1.waybill_no = t3.waybill_id
),
     unsign_summary_waybill as (
         select t1.waybill_no                                         as waybill_no
              , t1.input_time                                         as input_time
              , t1.taking_early_scantime                              as taking_early_scantime
              , t1.taking_early_scansitecode                          as taking_early_scansitecode
              , t1.deliver_early_scantime                             as deliver_early_scantime
              , t1.deliver_early_scansitecode                         as deliver_early_scansitecode
              , t1.first_network_code                                 as first_network_code
              , t1.dispatch_network_code                              as dispatch_network_code
              , t1.sign_time                                          as sign_time
              , coalesce(t1.first_center_network_code, t2.in_to_code) as first_center_network_code
              , t1.first_center_arrival_early_time                    as first_center_arrival_early_time
              , t1.first_center_send_later_time                       as first_center_send_later_time
              , t1.second_center_network_code                         as second_center_network_code
              , t1.second_center_arrival_early_time                   as second_center_arrival_early_time
              , t1.second_center_send_later_time                      as second_center_send_later_time
              , t1.end_center_network_code                            as end_center_network_code
         from (
                  select waybill_no                                                                     as waybill_no
                       , input_time                                                                     as input_time
                       , input_network_code                                                             as input_network_code
                       , taking_later_scantime                                                          as taking_early_scantime
                       , taking_later_scansitecode                                                      as taking_early_scansitecode
                       , deliver_early_scantime                                                         as deliver_early_scantime
                       , deliver_early_scansitecode                                                     as deliver_early_scansitecode
                       , first_network_network_code                                                     as first_network_code
                       , first_center_network_code                                                      as first_center_network_code
                       , first_center_arrival_early_time                                                as first_center_arrival_early_time
                       , first_center_send_later_time                                                   as first_center_send_later_time
                       , second_center_network_code                                                     as second_center_network_code
                       , second_center_arrival_early_time                                               as second_center_arrival_early_time
                       , second_center_send_later_time                                                  as second_center_send_later_time
                       , coalesce(instore_early_scantime, sign_early_scantime, outstore_early_scantime) as sign_time
                       , dispatch_network_code                                                          as dispatch_network_code
                       , end_center_network_code                                                        as end_center_network_code
                       , ordersource_code                                                               as ordersource_code
                       , ordersource_name                                                               as ordersource_name
                  from jms_dwd.dwd_wide_unsign_summary_waybill_dt
                  where dt >= date_add('{{ execution_date | cst_ds }}', -30)
                    and dt <= '{{ execution_date | cst_ds }}'
                    and substr(input_time, 1, 10) >= date_add('{{ execution_date | cst_ds }}', -30)
                    and substr(input_time, 1, 10) <= '{{ execution_date | cst_ds }}'
                    and input_time is not null
--                     and ordersource_name = '桃花岛'
                    and order_source_code = 'D09'
              ) t1
                  left join
              (
                  select in_from_code, out_to_code, max(in_to_code) as in_to_code
                  from jms_dm.dm_route_fastest
                  where dt = '{{ execution_date | cst_ds }}'
                    and in_from_code is not null
                    and out_to_code is not null
                  group by in_from_code, out_to_code
              ) t2
              on nvl(t1.first_network_code, '-999') = t2.in_from_code
                  and nvl(t1.dispatch_network_code, '-999') = t2.out_to_code
     ),
     false_ship_delivery_cfg as (
         select substr(start_province, 1, 2)        as start_province
              , substr(sign_province, 1, 2)         as sign_province
              , substr(remote_province, 1, 2)       as remote_province
              , substr(extremely_remote_area, 1, 2) as extremely_remote_area
              , timeout_hours
         from jms_dim.dim_tab_false_ship_delivery_cfg
         where state = 1
           and PLATFORM = '桃花岛春节不打烊'
           and start_province is not null
           and sign_province is not null
         group by substr(start_province, 1, 2)
                , substr(sign_province, 1, 2)
                , substr(remote_province, 1, 2)
                , substr(extremely_remote_area, 1, 2)
                , timeout_hours
     )
insert
overwrite
table
jms_dm.dm_mng_thd_flase_send_waybill_detail_mid_dt
partition
(
dt
)
select t1.waybill_no                                                        as waybill_no                       --运单号
     , t1.input_time                                                        as input_time                       --录入时间
     , t1.send_station_network_code                                         as send_station_network_code        --当前站点code
     , t1.send_station_network_name                                         as send_station_network_name        --当前站点code
     , t1.send_station_network_type                                         as send_station_network_type        --当前站点类型
     , t1.send_station_send_later_time                                      as send_station_send_later_time     --当前站点发件时间
     , t1.send_station_arrival_early_time                                   as send_station_arrival_early_time  --当前站点到件时间
     , t1.rn                                                                as rn                               --排序
     , t1.no_send_arr_flag                                                  as no_send_arr_flag                 --无到发时间
     , t1.min_send_arr                                                      as min_send_arr                     --到发最小
     , t1.min_scan_time                                                     as min_scan_time                    --操作最小
     , t1.next_station_network_code                                         as next_station_network_code        --下一站点code
     , t1.next_station_network_type                                         as next_station_network_type        --下一站点类型
     , t1.next_station_send_later_time                                      as next_station_send_later_time     --下一站点发件时间
     , t1.next_station_arrival_early_time                                   as next_station_arrival_early_time  --下一站点到件时间
     , t1.first_scan_time                                                   as first_scan_time                  --首机构扫描时间
     , t1.first_network_code                                                as first_network_code               --首机构网点
     , t1.first_network_name                                                as first_network_name               --首机构网点
     , t1.taking_early_scantime                                             as taking_early_scantime            --揽收时间
     , t1.taking_early_scansitecode                                         as taking_early_scansitecode        --揽收网点
     , t1.deliver_early_scantime                                            as deliver_early_scantime           --出仓时间
     , t1.deliver_early_scansitecode                                        as deliver_early_scansitecode       --出仓网点
     , t1.deliver_early_scansitename                                        as deliver_early_scansitename       --出仓网点name
     , t1.dispatch_network_code                                             as dispatch_network_code            --派件网点
     , t1.dispatch_network_name                                             as dispatch_network_name            --派件网点
     , t1.sign_time                                                         as sign_time                        --签收时间
     , t1.first_center_network_code                                         as first_center_network_code        --第一中心code
     , t1.first_center_arrival_early_time                                   as first_center_arrival_early_time  --第一中心到件时间
     , t1.first_center_send_later_time                                      as first_center_send_later_time     --第一中心发件时间
     , t1.second_center_network_code                                        as second_center_network_code       --第二中心code
     , t1.second_center_arrival_early_time                                  as second_center_arrival_early_time --第二中心到件时间
     , t1.second_center_send_later_time                                     as second_center_send_later_time    --第二中心发件时间
     , t1.end_center_network_code                                           as end_center_network_code          --末端网点
     , t1.first_center_provider_id                                          as first_center_provider_id         --始发中心省份
     , t1.first_center_provider_desc                                        as first_center_provider_desc       --始发中心省份
     , t1.second_center_provider_id                                         as second_center_provider_id        --第二中心省份
     , t1.second_center_provider_desc                                       as second_center_provider_desc      --第二中心省份
     , if(t1.first_center_provider_id = t1.second_center_provider_id, 1, 0) as is_same_prov_flag                --1第一第二中心是同一省份
     , t1.first_network_provider_id                                         as first_network_provider_id        --始发机构省份id
     , t1.first_network_provider_desc                                       as first_network_provider_desc      --始发机构省份desc
     , t1.first_network_city_id                                             as first_network_city_id            --始发机构城市id
     , t1.first_network_city_desc                                           as first_network_city_desc          --始发机构城市desc
     , t1.first_network_agent_code                                          as first_network_agent_code         --始发机构代理区code
     , t1.first_network_agent_name                                          as first_network_agent_name         --始发机构代理区name
     , t1.dispatch_network_provider_id                                      as dispatch_network_provider_id     --派件机构省份id
     , t1.dispatch_network_provider_desc                                    as dispatch_network_provider_desc   --派件机构省份desc
     , t1.dispatch_network_city_id                                          as dispatch_network_city_id         --派件机构城市id
     , t1.dispatch_network_city_desc                                        as dispatch_network_city_desc       --派件机构城市desc
     , t1.dispatch_network_agent_code                                       as dispatch_network_agent_code      --派件机构代理区code
     , t1.dispatch_network_agent_name                                       as dispatch_network_agent_name      --派件机构代理区name
     , t1.deliver_provider_id                                               as deliver_provider_id              --出仓网点省份id
     , t1.deliver_provider_desc                                             as deliver_provider_desc            --出仓网点省份desc
     , t1.deliver_city_id                                                   as deliver_city_id                  --出仓网点城市id
     , t1.deliver_city_desc                                                 as deliver_city_desc                --出仓网点城市desc
     , t2.timeout_hours                                                     as remote_timeout                   --偏远地区时长
     , t3.timeout_hours                                                     as extremely_remote_timeout         --级偏远地区时长
     , t4.timeout_hours                                                     as timeout                          --地区时长
     , substr(t1.input_time, 1, 10)                                         as date_time                        --日期
     , substr(t1.input_time, 1, 10)                                         as dt
from (
    select t1.waybill_no                       as waybill_no
         , t1.input_time                       as input_time
         , t2.send_station_network_code        as send_station_network_code       --当前站点code
         , t2.send_station_network_name        as send_station_network_name       --当前站点code
         , t2.send_station_network_type        as send_station_network_type       --当前站点类型
         , t2.send_station_send_later_time     as send_station_send_later_time    --当前站点发件时间
         , t2.send_station_arrival_early_time  as send_station_arrival_early_time --当前站点到件时间
         , t2.rn                               as rn                              --排序
         , t2.no_send_arr_flag                 as no_send_arr_flag                --无到发时间
         , t2.min_send_arr                     as min_send_arr                    --到发最小
         , t2.min_scan_time                    as min_scan_time                   --操作最小
         , t2.next_station_network_code        as next_station_network_code       --下一站点code
         , t2.next_station_network_type        as next_station_network_type       --下一站点类型
         , t2.next_station_send_later_time     as next_station_send_later_time    --下一站点发件时间
         , t2.next_station_arrival_early_time  as next_station_arrival_early_time --下一站点到件时间
         , t2.first_scan_time                  as first_scan_time                 --首机构扫描时间
         , t2.first_network_code               as first_network_code              --首机构网点

         , t1.taking_early_scantime            as taking_early_scantime
         , t1.taking_early_scansitecode        as taking_early_scansitecode
         , t1.deliver_early_scantime           as deliver_early_scantime
         , t1.deliver_early_scansitecode       as deliver_early_scansitecode
         , t1.dispatch_network_code            as dispatch_network_code
         , t1.sign_time                        as sign_time
         , t1.first_center_network_code        as first_center_network_code
         , t1.first_center_arrival_early_time  as first_center_arrival_early_time
         , t1.first_center_send_later_time     as first_center_send_later_time
         , t1.second_center_network_code       as second_center_network_code
         , t1.second_center_arrival_early_time as second_center_arrival_early_time
         , t1.second_center_send_later_time    as second_center_send_later_time
         , t1.end_center_network_code          as end_center_network_code
         , t3.provider_id                      as first_center_provider_id
         , t3.provider_desc                    as first_center_provider_desc
         , t4.provider_id                      as second_center_provider_id
         , t4.provider_desc                    as second_center_provider_desc
         , t5.name                             as first_network_name
         , t5.provider_id                      as first_network_provider_id
         , t5.provider_desc                    as first_network_provider_desc
         , t5.city_id                          as first_network_city_id
         , t5.city_desc                        as first_network_city_desc
         , t5.agent_code                       as first_network_agent_code
         , t5.agent_name                       as first_network_agent_name
         , t6.provider_id                      as dispatch_network_provider_id
         , t6.provider_desc                    as dispatch_network_provider_desc
         , t6.city_id                          as dispatch_network_city_id
         , t6.city_desc                        as dispatch_network_city_desc
         , t6.agent_code                       as dispatch_network_agent_code
         , t6.agent_name                       as dispatch_network_agent_name
         , t6.name                             as dispatch_network_name
         , t7.provider_id                      as deliver_provider_id
         , t7.provider_desc                    as deliver_provider_desc
         , t7.city_id                          as deliver_city_id
         , t7.city_desc                        as deliver_city_desc
         , t7.name                             as deliver_early_scansitename
    from unsign_summary_waybill t1
             join unsign_trace_waybill_step t2
                  on t1.waybill_no = t2.waybill_no
             left join jms_dim.dim_network_whole_massage t3
                       on t1.first_center_network_code = t3.code
             left join jms_dim.dim_network_whole_massage t4
                       on t1.second_center_network_code = t4.code
             left join jms_dim.dim_network_whole_massage t6
                       on t1.dispatch_network_code = t6.code
             left join jms_dim.dim_network_whole_massage t7
                       on t1.deliver_early_scansitecode = t7.code
             left join jms_dim.dim_network_whole_massage t5
                       on t2.first_network_code = t5.code
) t1
         left join false_ship_delivery_cfg t2
                   on substr(t1.first_center_provider_desc, 1, 2) = t2.remote_province
         left join false_ship_delivery_cfg t3
                   on substr(t1.first_center_provider_desc, 1, 2) = t3.extremely_remote_area
         left join false_ship_delivery_cfg t4
                   on substr(t1.first_network_provider_desc, 1, 2) = t4.start_province
                       and substr(t1.dispatch_network_provider_desc, 1, 2) = t4.sign_province
    distribute by dt
   , pmod(hash(waybill_no), 200)
;
